package org.bugjail.dataaccess.jdbc.objects;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import org.bugjail.data.SimpleTestCase;
import org.bugjail.data.Suite;
import org.bugjail.data.TestCase;
import org.bugjail.data.TestRound;
import org.bugjail.dataaccess.DAOFactory;
import org.bugjail.dataaccess.jdbc.connection.QueryException;
import org.bugjail.dataaccess.jdbc.connection.QueryManager;
import org.bugjail.dataaccess.objects.SuiteDAO;
import org.bugjail.dataaccess.objects.TestCaseDAO;

class JDBCTestCaseDAO implements TestCaseDAO {

	private final String PROPERTIES_FILE = "/org/bugjail/dataaccess/jdbc/properties/testcasedao.properties";

	private final String PROPERTY_RETRIEVE_BY_SUITE = "retrieve.bysuite";
	private final String PROPERTY_RETRIEVE_BY_SUITE_NOT_ON_ROUND = "retrieve.bysuite.notonround";
	private final String PROPERTY_RETRIEVE_BY_ID = "retrieve.byid";
	private final String PROPERTY_RETRIEVE_BY_ID_AND_VERSION = "retrieve.byidandversion";
	private final String PROPERTY_INSERT_SIMPLE = "insert.simple";
	private final String PROPERTY_UPDATE_SIMPLE = "update.simple";
	
	private TestCase extractTestCaseFromResultSet(ResultSet rs, QueryManager queryManager, Suite suite) throws SQLException {
		TestCase testCase = new SimpleTestCase();
		int id = rs.getInt("id");
		testCase.setId(id);

		if (suite == null) {
			SuiteDAO suiteDao = DAOFactory.getDAOFactory().getSuiteDAO();
			testCase.setSuite(suiteDao
					.retrieveById(rs.getInt("suite_id")));
		} else {
			testCase.setSuite(suite);
		}
		testCase.setName(rs.getString("name"));
		testCase.setObjective(rs.getString("objective"));
		testCase.setSeverity(rs.getInt("severity"));
		testCase.setEstimatedTime(rs.getInt("estimatedtime"));
		testCase.setStatus(rs.getInt("status"));
		testCase.setVersion(rs.getInt("version"));

		return testCase;
	}
	
	@Override
	public void create(TestCase element) {
		if (element instanceof SimpleTestCase) {
			insertSimpleTestCase((SimpleTestCase)element);
		}
		// TODO Auto-generated method stub
		
	}
	
	private void insertSimpleTestCase(SimpleTestCase tc) {
		QueryManager queryManager = null;
		try {
			try {
				queryManager = new QueryManager(PROPERTIES_FILE);
				PreparedStatement statement = queryManager
						.getPreparedStatement(PROPERTY_INSERT_SIMPLE);
				statement.setString(1, tc.getName());
				statement.setString(2, tc.getObjective());
				statement.setInt(3, tc.getSeverity());
				statement.setInt(4, tc.getStatus());
				statement.setInt(5, tc.getSuite().getId());
				statement.setInt(6, tc.getEstimatedTime());
				statement.executeUpdate();

				QueryManager.commit(queryManager);

			} finally {
				QueryManager.close(queryManager);
			}
		} catch (Exception e) {
			QueryManager.rollback(queryManager);
			throw new QueryException(e.getMessage());
		}
	}
	
	private void updateSimpleTestCase(SimpleTestCase tc) {
		QueryManager queryManager = null;
		try {
			try {
				queryManager = new QueryManager(PROPERTIES_FILE);
				PreparedStatement statement = queryManager
						.getPreparedStatement(PROPERTY_UPDATE_SIMPLE);
				statement.setString(1, tc.getName());
				statement.setString(2, tc.getObjective());
				statement.setInt(3, tc.getSeverity());
				statement.setInt(4, tc.getStatus());
				statement.setInt(5, tc.getSuite().getId());
				statement.setInt(6, tc.getEstimatedTime());
				statement.setInt(7, tc.getId());
				statement.setInt(8, tc.getVersion());
				statement.executeUpdate();

				QueryManager.commit(queryManager);

			} finally {
				QueryManager.close(queryManager);
			}
		} catch (Exception e) {
			QueryManager.rollback(queryManager);
			throw new QueryException(e.getMessage());
		}
	}

	@Override
	public void delete(TestCase element) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public TestCase retrieveById(int id) {
		TestCase testCase = null;
		QueryManager queryManager = null;
		try {
			try {
				queryManager = new QueryManager(PROPERTIES_FILE);
				PreparedStatement statement = queryManager
						.getPreparedStatement(PROPERTY_RETRIEVE_BY_ID);
				statement.setInt(1, id);
				ResultSet rs = statement.executeQuery();
				if (rs.next()) {
					testCase = extractTestCaseFromResultSet(rs, queryManager, null);
				}
			} finally {
				QueryManager.close(queryManager);
			}
		} catch (Exception e) {
			throw new QueryException(e.getMessage());
		}
		return testCase;
	}

	@Override
	public List<TestCase> retrieveByName(String name) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void update(TestCase element) {
		if (element instanceof SimpleTestCase) {
			updateSimpleTestCase((SimpleTestCase)element);
		}
		// TODO Auto-generated method stub
	}

	@Override
	public List<TestCase> retrieveAll() {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<TestCase> retrieveTestCasesOfSuite(Suite suite) {
		List<TestCase> testCases = new LinkedList<TestCase>();
		QueryManager queryManager = null;
		try {
			try {
				queryManager = new QueryManager(PROPERTIES_FILE);
				PreparedStatement statement = queryManager
						.getPreparedStatement(PROPERTY_RETRIEVE_BY_SUITE);
				statement.setInt(1, suite.getId());
				ResultSet rs = statement.executeQuery();
				while (rs.next()) {
					testCases.add(extractTestCaseFromResultSet(rs,
							queryManager, suite));
				}
			} finally {
				QueryManager.close(queryManager);
			}
		} catch (Exception e) {
			throw new QueryException(e.getMessage());
		}
		return testCases;
	}

	@Override
	public TestCase retrieveByIdAndVersion(int id, int version) {
		TestCase testCase = null;
		QueryManager queryManager = null;
		try {
			try {
				queryManager = new QueryManager(PROPERTIES_FILE);
				PreparedStatement statement = queryManager
						.getPreparedStatement(PROPERTY_RETRIEVE_BY_ID_AND_VERSION);
				statement.setInt(1, id);
				statement.setInt(2, version);
				ResultSet rs = statement.executeQuery();
				if (rs.next()) {
					testCase = extractTestCaseFromResultSet(rs, queryManager, null);
				}
			} finally {
				QueryManager.close(queryManager);
			}
		} catch (Exception e) {
			throw new QueryException(e.getMessage());
		}
		return testCase;
	}

	@Override
	public List<TestCase> retrieveTestCasesOfSuiteNotOnRound(Suite suite,
			TestRound testRound) {
		List<TestCase> testCases = new LinkedList<TestCase>();
		QueryManager queryManager = null;
		try {
			try {
				queryManager = new QueryManager(PROPERTIES_FILE);
				PreparedStatement statement = queryManager
						.getPreparedStatement(PROPERTY_RETRIEVE_BY_SUITE_NOT_ON_ROUND);
				statement.setInt(1, suite.getId());
				statement.setInt(2, testRound.getId());
				ResultSet rs = statement.executeQuery();
				while (rs.next()) {
					testCases.add(extractTestCaseFromResultSet(rs,
							queryManager, suite));
				}
			} finally {
				QueryManager.close(queryManager);
			}
		} catch (Exception e) {
			throw new QueryException(e.getMessage());
		}
		return testCases;
	}
	
}